﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;

namespace DoeObjects
{
    public class Author
    {
        public int Aid { get; set; }
        public string Name { get; set; }
        public bool isEditor { get; set; }

        public static Author getByAid(int aid)
        {           
            string SQL = "SELECT * FROM [AUTHOR] WHERE aid = @aid";
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand(SQL, connection);
            cmd.Parameters.AddWithValue("aid", aid);

            Author author = null;
            try
            {
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    author = getCurrentAuthor(reader);
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return author;

        }

        public static List<Author> getAuthorsFromBook(int titleId)
        {
            List<Author> authors = new List<Author>();

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM Author INNER JOIN title_has_authors AS tha ON author.Aid = tha.Aid WHERE tha.IsEditor = 0 AND tha.titleId = @id", connection);
            cmd.Parameters.AddWithValue("@id", titleId);

            try
            {
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    authors.Add(getCurrentAuthor(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return authors;
        }

        public static List<Author> getEditorsFromBook(int titleId)
        {
            List<Author> editors = new List<Author>();

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM Author INNER JOIN title_has_authors AS tha ON author.Aid = tha.Aid WHERE tha.IsEditor = 1 AND tha.titleId = @id", connection);
            cmd.Parameters.AddWithValue("@id", titleId);

            try
            {
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    editors.Add(getCurrentAuthor(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return editors;
        }

        public static List<Author> getAll()
        {
            List<Author> authors = new List<Author>();

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM Author", connection);

            try
            {
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    authors.Add(getCurrentAuthor(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return authors;
        }

        /// <summary>
        /// returns all authors where the names matches to the given pattern. (for search)
        /// </summary>
        /// <param name="firstAndLastName"></param>
        /// <returns></returns>
        public static List<Author> getByName(string firstAndLastName)
        {
            List<Author> authors = new List<Author>();

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM Author WHERE LOWER(Name) LIKE @name", connection);

            SqlParameter nameParam = new SqlParameter("@name", SqlDbType.VarChar);
            nameParam.Value = '%' + firstAndLastName.ToLower().Replace(' ', '%') + '%';
            cmd.Parameters.Add(nameParam);

            try
            {
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    authors.Add(getCurrentAuthor(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return authors;
        }

        public static List<Author> getByName(string fullname, bool useLikeExpression)
        {
            List<Author> authors = new List<Author>();

            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM Author WHERE Name LIKE @fullName", connection);

            SqlParameter fullParam = new SqlParameter("@fullName", SqlDbType.VarChar);
            if (useLikeExpression)
            {
                fullParam.Value = '%' + fullname + '%';
            }
            else
            {
                fullParam.Value = fullname;
            }
            cmd.Parameters.Add(fullParam);


            try
            {
                connection.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    authors.Add(getCurrentAuthor(reader));
                }
                reader.Close();
            }
            catch (SqlException ex)
            {
                //something bad happened.
                //TODO: log exception
                throw ex;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
            return authors;
        }

        /// <summary>
        /// creates a authorobject based on the data from a datareader
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        internal static Author getCurrentAuthor(SqlDataReader reader)
        {
            Author author = null;

            if (reader != null)
            {
                author = new Author();
                author.Aid = (int)reader["aid"];
                author.Name = reader["Name"] as string;
            }

            return author;
        }

        /// <summary>
        /// adds a new author into the database and returns the inserted id. if it returnes 0 the insert failed
        /// </summary>
        /// <param name="author"></param>
        /// <returns></returns>
        public static int add(Author author)
        {
            int lastInsertedId = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("INSERT INTO author (Name) OUTPUT INSERTED.Aid VALUES (@FullName)", connection);

            SqlParameter fullNameParameter = new SqlParameter("@FullName", SqlDbType.VarChar);
            fullNameParameter.Value = author.Name;
            cmd.Parameters.Add(fullNameParameter);

            try
            {
                connection.Open();
                Object res = cmd.ExecuteScalar();
                if (res != null)
                {
                    lastInsertedId = (int)res;
                }
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return lastInsertedId;
        }


        /// <summary>
        /// updates an author in the database
        /// </summary>
        /// <param name="author"></param>
        /// <returns></returns>
        public static bool update(Author author)
        {
            int affectedRows = 0;
            SqlConnection connection = new SqlConnection(Settings.ConnectionString);
            SqlCommand cmd = new SqlCommand("UPDATE author SET Name = @FullName WHERE aid = @id", connection);
            cmd.Parameters.AddWithValue("@id", author.Aid);

            SqlParameter fullNameParameter = new SqlParameter("@FullName", SqlDbType.VarChar);
            fullNameParameter.Value = author.Name;
            cmd.Parameters.Add(fullNameParameter);

            try
            {
                connection.Open();
                affectedRows = cmd.ExecuteNonQuery();
            }
            catch (SqlException er)
            {
                //oo
                throw er;
            }
            finally
            {
                if (connection != null && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }

            return (affectedRows > 0);
        }

        /// <summary>
        /// saves the author in the database. when Aid == 0, the an insert will be performed and the Aid field in the authorobject updated. otherwise a update will be executed
        /// </summary>
        /// <returns></returns>
        public bool save()
        {
            if (this.Aid == 0)
            {
                this.Aid = Author.add(this);
                return (this.Aid != 0);
            }
            else
            {
                return Author.update(this);
            }
        }
    }
}
